CREATE PROCEDURE [dbo].[asi_GetPublishRequestStatus]
    @PublishRequestKey uniqueidentifier,
    @PublishServerCode nchar(1) AS
BEGIN

SELECT
(SELECT Count(*) FROM PublishRequestDetail
  WHERE PublishRequestKey = a.PublishRequestKey
    AND PublishServerCode = @PublishServerCode
    AND PublishRequestStatusCode = 0) AS PendingCount,
(SELECT Count(*) FROM PublishRequestDetail
  WHERE PublishRequestKey = a.PublishRequestKey
    AND PublishServerCode = @PublishServerCode
    AND PublishRequestStatusCode = 1) AS PublishedCount,
(SELECT Count(*) FROM PublishRequestDetail
  WHERE PublishRequestKey = a.PublishRequestKey
    AND PublishServerCode = @PublishServerCode
   AND PublishRequestStatusCode = 2) AS CanceledCount,
(SELECT Count(*) FROM PublishRequestDetail
  WHERE PublishRequestKey = a.PublishRequestKey
    AND PublishServerCode = @PublishServerCode
    AND PublishRequestStatusCode = 3)  AS FailedCount
FROM PublishRequest a
WHERE PublishRequestKey = @PublishRequestKey
END

GO